GROUPING[_ID]
Purpose
With the use of this function you can distinguish between regular result rows and super-aggregate rows which are
created in case of GROUPING SETS
, CUBE
or ROLLUP
clauses.
Syntax
grouping::=
Usage Notes
- Each argument must be similar to an expression within the
GROUP BY
clause. - In case of a single argument the result value is 0 if the corresponding grouping considers this expression, otherwise 1 (superaggregation).
- In case of multiple arguments the result value is a number whose binary representation is similar to
GROUPING(arg1), GROUPING(arg2),..., GROUPING(argn)
. For example, the following is true:GROUPING(a,b,c) = 4xGROUPING(a) + 2xGROUPING(b) + 1xGROUPING(c)
- For more information on
GROUPING SETS
,CUBE
andROLLUP
, refer to the SELECT statement in the Query language (DQL) section.
Example
SELECT SUM(volume) revenue, y, m,
DECODE(GROUPING(y,m),1,'yearly',3,'total',NULL) superaggregate
FROM sales GROUP BY ROLLUP(y,m) ORDER BY y,revenue;
Result
REVENUE | Y | M | SUPERAGGREGATE |
1725.90 | 2010 | December | |
1725.90 | 2010 | yearly | |
735.88 | 2011 | April | |
752.46 | 2011 | February | |
842.32 | 2011 | March | |
931.18 | 2011 | January | |
3261.84 | 2011 | yearly | |
4987.74 | total |